A database monitor is a computer program that measures the activity of a database management system and displays those measurements in a meaningful way so you can see everything's OK... or quickly learn about problems and threats to performance and availability.

Foxhound 4 is a third-party database monitor for SAP® SQL Anywhere®. Here's how it works:

• Every 10 seconds Foxhound retrieves performance statistics from your database.

• Foxhound then
  • stores these statistics in its own SQL Anywhere 16 database,

  • performs summarization and other value-added calculations, and

  • displays the results via HTML using SQL Anywhere's built-in HTTP server.

Figure 1 shows the Foxhound Monitor page for a lightly-loaded SQL Anywhere server with 135 connections, one of which is blocked by an uncommitted operation made by a long-running transaction on another connection.

Table 1 compares Foxhound Version 4 with several alternative products:

• the SQL Anywhere Monitor that is available with certain SQL Anywhere 17 editions,

• the Sybase Central Performance Monitor that comes with SQL Anywhere 16,

• the SQL Anywhere Cockpit that comes with SQL Anywhere 17,

• the DBConsole utility that also comes with SQL Anywhere 16 and

• the Windows Performance Monitor (PERFMON) that is supported by SQL Anywhere 17.

Here's a list of Foxhound characteristics you can take advantage of:

1. Functionality out of the box  Foxhound just requires a connection string or DSN to get started; the next thing you see is your database in the Foxhound Monitor page; there are no widgets or wizards to deal with, and no need to pick and choose which statistics to display.

2. Guidance  Foxhound provides on-screen guidance for all the data it displays and all the options it offers. The column titles, tooltip text, field descriptions, context-sensitive Help and performance tips are all designed to help you understand what you are looking at and what you can do about it.

3. Information at a glance  Foxhound presents a straightforward interface for professional developers with no graphs, waveforms or "data visualization" artwork. All the relevant data and value-added calculations are gathered together on single pages with peak highlighting and hypertext links to scroll through history and open different views in new browser tabs.

4. Open access  Foxhound provides read-only SQL access to all the historical data pertaining to your database: it's your data, you own it. The Foxhound Help contains sample queries, and to make your own queries easier to write important internal primary key values are displayed together with the data on the Monitor and other pages; e.g., sampling_id, sample_set_number and the new connection_id_string.

5. Legacy support  Foxhound supports target databases running all versions of SQL Anywhere from 6 to 17. And while it's true that older versions of SQL Anywhere don't provide (and Foxhound doesn't display) modern performance statistics, statistics that are available do get displayed, not lowest common denominators.

6. Robustness  The Foxhound Monitor keeps on trying to connect to the target database during an outage. Foxhound also silently handles invalid performance statistics, and it keeps working after it throws an exception, thus meeting the definition of robustness: "The degree to which a system or component can function correctly in the presence of invalid inputs or stressful environmental conditions."

7. Dogfooding  Foxhound is regularly run against the Foxhound database itself; to test new features, sure, but primarily to look for performance bottlenecks. Foxhound is also the first tool of choice on client performance and tuning assignments; that's how the Foxhound Monitor was created in the first place, on assignment in 2003 as a Blocked Connection Display for a SQL Anywhere 9 database at UNO Money Transfers. The Schema Display feature was created even earlier, in 1998, for an immense Oracle database at Princess Margaret Hospital... which makes 2018 the twentieth anniversary of Foxhound!

The Menu page is Foxhound's home page. The DSN tab in Figure 3 lets you pick a target database from a drop-down list of ODBC DSNs and then click on the Monitor Database button.

Alternatively, the String tab in Figure 4 lets you create a DSN-less connection to a target database.

The Monitor page is automatically refreshed every 10 seconds to show a snapshot of current activity: the most recent 10 samples plus the most recent details of 10 connections.

Figure 5 shows a busy primary database in a SQL Anywhere 16 High Availability setup. One Alert has been issued: Alert #14 Unscheduled requests indicates there's a bottleneck in satisfying client requests. The Max Req column shows that SQL Anywhere's "AutoMultiProgrammingLevel" has been adjusting the maximum number of tasks that can be active (Max Req is currently 22) but there's a serious backlog (Unsch Req 73).

The Monitor page is filled with hypertext links, most of them opening new tabs showing historical data:

• The Recent Samples links like 10:03:05 AM open the Sample History page positioned on the selected sample, and

• the connection links like 489 / h.barbosa / Breck / - / app open the Connection History page for the selected connection.

Sample History and Connection History pages are described in the next two sections.

---

Tip: The 10-second refresh cycle of the Monitor page is not related to the 10-second Foxhound sampling interval. The underlying Foxhound sampling process continues until instructed to stop, whether or not the Monitor page is displayed at all.

The connection links like 489 / h.barbosa / Breck / - / app open a Connection History page for the selected connection in a new tab. This "drill down" view is shown in the next section.

Figure 7 shows yet another view of the busy server in Figures 5 and 6. The Connection History page shows the history of performance statistics for one single connection over time. A snapshot of the most recent server and database statistics is included at the top of the page, but the rest of the page is devoted to that one connection.

Alert messages are displayed Connection History even when they have nothing to do with connection being displayed because it's always important to know what's going on.

Each History link opens a Sample History page in a new tab, positioned to the same sample, in effect returning to the "big picture" view shown in the previous section.

---

Tip: The Connection Id String uniquely identifies a connection by connection number and login time so the Connection History page doesn't mix up different connections with the same connection number. It is also useful for adhoc queries; that's the only reason it's displayed on the Connection History page, so you can copy and paste the value into your ISQL session.

Figure 8 shows an Alert email for a connection that has violated a strict limit placed on the amount of temporary space a single connection can use; in this case, 100M for 3 or more samples (about 30 seconds).

The email subject line is a highly condensed summary:

What happened?	Alert #22 Conn temp file usage
Where did it happen?	Inventory
Where exactly was that?	Machine XPS, Server inventory16_xps, Database inventory16

As well as describing this Alert, the body of the email contains a list of recent alerts for this database and a snapshot of the Monitor page when this Alert was issued.

Table 2 shows the default definitions for all the Alert conditions supported in Foxhound 4. The values shown in [square braces] can be changed for each target databases, except for [server2] which is determined at runtime.

Table 2. Default Alert Criteria

1 Database unresponsive	Foxhound has been unable to gather samples for [1m] or longer.
2 Long heartbeat	The heartbeat time has been [1s] or longer for [10] or more recent samples.
3 Long sample time	The sample time has been [10s] or longer for [10] or more recent samples.
4 CPU usage	The CPU time has been [90]% or higher for [10] or more recent samples.
5 Database disk space	The free disk space on the drive holding the main database file has fallen below [1G].
6 Temp disk space	The free disk space on the drive holding the temporary file has fallen below [1G].
7 Log disk space	The free disk space on the drive holding the transaction log file has fallen below [1G].
8 Other disk space	The free disk space on one or more drives holding other database files has fallen below [1G].
9 Arbiter unreachable	The high availability target database has become disconnected from the arbiter server.
10 Partner unreachable	The high availability target database has become disconnected from the partner database.
11 ServerName change	The real server name has changed to [server2], possibly because of an HA failover or OnDemand move.
13 File fragmentation	There are [1,000] or more fragments in the main database file.
14 Unscheduled requests	The number of requests waiting to be processed has reached [5] or more for [10] or more recent samples.
15 Incomplete I/Os	The current number of incomplete file reads and/or writes has reached [2] or more for [10] or more recent samples.
16 I/O operations	There have been [1,000] or more disk and log I/O operations per second for [10] or more recent samples.
17 Checkpoint urgency	The Checkpoint Urgency has been [100]% or more for [10] or more recent samples.
18 Recovery urgency	The Recovery Urgency has been [1,000]% or more for [10] or more recent samples.
19 Cache size	The cache has reached [100]% of its maximum size for [10] or more recent samples.
20 Cache satisfaction	The cache satisfaction (hits/reads) has fallen to [90]% or lower for [10] or more recent samples.
21 Temp file usage	The total temporary file space used by all connections has been [1G] or larger for [10] or more recent samples.
22 Conn temp file usage	At least one single connection has used [512M] or more of temporary file space during [10] or more recent samples.
23 Blocked connections	The number of blocked connections has reached [10] or more during [10] or more recent samples.
24 Conn blocking others	At least one single connection has blocked [5] or more other connections during [10] or more recent samples.
25 Locks	The number of locks has reached [1,000,000] or more during [10] or more recent samples.
26 Connections	The number of connections has reached [1,000] or more for [10] or more recent samples.
27 Connection CPU	The approximate CPU time has been [25]% or higher for at least one connection during [10] or more recent samples.
28 Long transaction	The transaction running time has reached [1m] or more for at least one connection during [10] or more recent samples.
29 Cache panics	There have been [1] or more cache panics per second for [10] or more recent samples.
30 Database read-only	The target database has changed from accepting updates to read-only processing.
31 Database updatable	The target database has changed from read-only processing to accepting updates.
32 Rollback log usage	The total rollback log space used by all connections has been [1G] or larger for [10] or more recent samples.
33 Uncommitted operations	The total number of uncommitted operations for all connections has reached [1,000,000] or more during [10] or more recent samples.
34 Long uncommitted	The number of uncommitted operations has reached [1,000,000] or more while the transaction running time has reached [1m] or more for at least one connection.
35 Separate ping failed	Foxhound has been unable to make a separate ping connection to the target database for [10] or more recent samples.

For every target database being monitored by Foxhound, four separate schedules may be created to turn specific features on and off in 15-minute intervals over a 7-day week:

• start and stop the entire monitor process (as shown in Figure 9 below),

• start and stop the gathering of connection-level statistics for each sample,

• start and stop the sending of Alert emails, and

• start and stop the AutoDrop process that deals with runaway connections.

Figure 10 shows a single database connection that is using up all the CPU time (92% of 8 processors). The Parent, Child Conns column shows there are 3 external database connections and 8 internal child connections, and the connections frame at the bottom of the page shows that connection 5 is the external connection responsible for creating all 8 internal connections named "INT: EXCHANGE". The Last Plan Text field shows why: SQL Anywhere's intra-query parallelism feature is in full force with all 8 processors devoted to performing a "Parallel Index-Only Scan".

In this case, the details of the "why" may not be as important as the "what": Connection 5 is a runaway. A quick solution might be to drop connection 5, and Foxhound's AutoDrop feature can be used to automate that solution. Another solution might be to "turn down the volume" on the intra-query parallelism feature by setting the MAX_QUERY_TASKS database option to something other than "all processors"; e.g., 4, or even 1 to turn it off altogether. Other solutions might be to change the query to be less aggressive, or schedule it in off-peak hours.

Figure 11 shows a SQL Anywhere 16 server that is behaving erratically in the face of a steady load:

• The Interval column diverges from the expected 10-seconds-per-sample rate,

• the Response and Throughput columns fluctuate wildly, and

• CPU usage varies up and down between 5% and 50% even though the workload remains constant.

The Alert #22 reveals the culprit: A single connection that is consuming Temp Space at a great rate, causing great difficulties for the other 100 connections that are trying to get something done. The other values (CPU usage, response time, etc) aren't the problem, they're symptoms of runaway Temp Space usage.

SQL Anywhere makes it easy to deploy large numbers of separate database servers on the same and different computers, each one running multiple databases. Foxhound deals with large deployments in a number of ways:

• The Manage Multiple Monitor Sessions facility lets you define a connection strings file to make it easier to start and stop Foxhound monitor session for large numbers of target databases.

• The Save and Restore Monitor Options facility lets you manipulate the Monitor Options settings for each target database, plus a separate set of "Default Settings", in ways that make it easier to deal with large deployments. In particular, you can edit the Default Settings separately from actual target databases, then use the Force Default Settings on All Targets button to publish the same set of Monitor Options to all the actual Foxhound monitor sessions.

• The Monitor tab on the Foxhound Menu page (shown in Figure 12) shows all the Foxhound monitor sessions at a glance.